﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Portal.Database.Service;
using Portal.Web.Utility;
using Portal.Core;
using Portal.Cms.Model;

namespace Portal.Cms.Bussiness
{
    public class WeeklyScheduleBll
    {
        public WeeklyScheduleCls[] ReadingWeeklySchedule(string SiteId, string Keyword)
        {
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);

            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string Query = GetSelectQuery() +
                           " where AssetCode='"+AssetCode+"' order by WeekNumber DESC,[Year] DESC";
            DataTable dt = DBService.GetDataTable(null, Query, null);
            WeeklyScheduleCls[] WeeklySchedules = WeeklyScheduleParser.ParseFromDataTable(dt);
            return WeeklySchedules;
        }

        public static string GetSelectQuery()
        {
            string Query = "select TableWeeklySchedule.* from TableWeeklySchedule";
            return Query;
        }

        public WeeklyScheduleCls CreateWeeklyScheduleModel(string SiteId, string WeeklyScheduleId)
        {
            string Query = GetSelectQuery() +
                          "     where WeeklyScheduleId=@WeeklyScheduleId;" +
                          " select * from TableWeeklyScheduleItem where frkWeeklyScheduleId=@WeeklyScheduleId order by DayOfWeek,Time;";
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DataSet ds = DBService.GetDataSet(null, Query, new DbParam[]{
                new DbParam("WeeklyScheduleId",WeeklyScheduleId)
            });
            if (ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            string Xml = WeeklyScheduleParser.GetXml(ds.Tables[0].Rows[0]);
            string XmlData = ds.Tables[0].Rows[0]["XmlData"].ToString();
            WeeklyScheduleCls OWeeklySchedule = WeeklyScheduleParser.ParseFromXml(Xml);
            OWeeklySchedule.WeeklyScheduleItemText = XmlData;
            return OWeeklySchedule;
        }

        public WeeklyScheduleCls CreateWeeklyScheduleModelByWeekNo(string SiteId, int Year, int WeekNumber)
        {
            string Query = GetSelectQuery() +
                           "     where WeekNumber=" + WeekNumber + " and [Year]=" + Year + "; " +
                           " select TableWeeklyScheduleItem.* from TableWeeklyScheduleItem " +
                           "     join TableWeeklySchedule on TableWeeklySchedule.WeeklyScheduleId=frkWeeklyScheduleId" +
                           "    where WeekNumber=" + WeekNumber + " and [Year]=" + Year + " order by DayOfWeek,Time;";
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DataSet ds = DBService.GetDataSet(null, Query, null);
            if (ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            string Xml = WeeklyScheduleParser.GetXml(ds.Tables[0].Rows[0]);
            WeeklyScheduleCls OWeeklySchedule = WeeklyScheduleParser.ParseFromXml(Xml);
            return OWeeklySchedule;
        }


        public static string CreateWeeklyScheduleModelByWeekNoForWs(string SiteId, int Year, int WeekNumber)
        {
            string Query = GetSelectQuery() +
                           "     where WeekNumber=" + WeekNumber + " and [Year]=" + Year + "; " +
                           " select TableWeeklyScheduleItem.* from TableWeeklyScheduleItem " +
                           "     join TableWeeklySchedule on TableWeeklySchedule.WeeklyScheduleId=frkWeeklyScheduleId" +
                           "    where WeekNumber=" + WeekNumber + " and [Year]=" + Year + " order by DayOfWeek,Time;";
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DataSet ds = DBService.GetDataSet(null, Query, null);
            if (ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            string Xml = WeeklyScheduleParser.GetXml(ds.Tables[0].Rows[0]);
            return Xml;
        }


        public void SaveWeeklySchedule(string SiteId, string WeeklyScheduleId, WeeklyScheduleCls OWeeklySchedule)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            IDbTransaction dbTrans = DBService.BeginTransaction();

            if (string.IsNullOrEmpty(OWeeklySchedule.WeekNumber)) throw new Exception("Chưa xác định tuần nhập lịch");
            if (string.IsNullOrEmpty(OWeeklySchedule.Year)) throw new Exception("Chưa xác định năm");
            WeeklyScheduleItemCls[] WeeklyScheduleItems = OWeeklySchedule.WeeklyScheduleItems;// WeeklyScheduleItemParser.ParseMultiRecordFromXml(OWeeklySchedule.WeeklyScheduleItemText);
            
            try
            {
                DBService.Update(
                    dbTrans, "TableWeeklySchedule", "WeeklyScheduleId", WeeklyScheduleId,
                    new DbParam[]{
                    new DbParam("WeekNumber",OWeeklySchedule.WeekNumber),
                    new DbParam("Year",OWeeklySchedule.Year),
                    new DbParam("Description",OWeeklySchedule.Description),
                    new DbParam("BelongSite","dhv"),
                    new DbParam("XmlData",OWeeklySchedule.WeeklyScheduleItemText),
                });

                DBService.ExecuteNonQuery(
                    dbTrans, "Delete from TableWeeklyScheduleItem where frkWeeklyScheduleId=@WeeklyScheduleId",
                    new DbParam[]
                    {
                        new DbParam("WeeklyScheduleId",OWeeklySchedule.WeeklyScheduleId),
                    });
                for (int iIndex = 0; iIndex < WeeklyScheduleItems.Length; iIndex++)
                {
                    DBService.Insert(
                       dbTrans, "TableWeeklyScheduleItem",
                       new DbParam[]
                       {
                            new DbParam("WeeklyScheduleItemId",System.Guid.NewGuid().ToString()),
                            new DbParam("ScheduleDate",WeeklyScheduleItems[iIndex].ScheduleDate),
                            new DbParam("DayOfWeek",WeeklyScheduleItems[iIndex].DayOfWeek),
                            new DbParam("Time",WeeklyScheduleItems[iIndex].Time),
                            new DbParam("ForeColor",WeeklyScheduleItems[iIndex].ForeColor),
                            new DbParam("BelongSite",WeeklyScheduleItems[iIndex].BelongSite),
                            new DbParam("frkWeeklyScheduleId",WeeklyScheduleItems[iIndex].frkWeeklyScheduleId),
                            new DbParam("ScheduleText",WeeklyScheduleItems[iIndex].ScheduleText),
                            new DbParam("Segment",WeeklyScheduleItems[iIndex].Segment),
                    });
                }
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                dbTrans.Rollback();
                throw (ex);
            }
        }


        public void AddWeeklySchedule(string SiteId, WeeklyScheduleCls OWeeklySchedule)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            IDbTransaction dbTrans = DBService.BeginTransaction();

            string WeeklyScheduleId = System.Guid.NewGuid().ToString();
            if (string.IsNullOrEmpty(OWeeklySchedule.WeekNumber)) throw new Exception("Chưa xác định tuần nhập lịch");
            if (string.IsNullOrEmpty(OWeeklySchedule.Year)) throw new Exception("Chưa xác định năm");
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);

            WeeklyScheduleItemCls[] WeeklyScheduleItems = OWeeklySchedule.WeeklyScheduleItems;// WeeklyScheduleItemParser.ParseMultiRecordFromXml(OWeeklySchedule.WeeklyScheduleItemText);
            
            try
            {
                DBService.Insert(null, "TableWeeklySchedule",
                    new DbParam[]
                    {
                        new DbParam("WeeklyScheduleId",WeeklyScheduleId),
                        new DbParam("WeekNumber",OWeeklySchedule.WeekNumber),
                        new DbParam("Year",OWeeklySchedule.Year),
                        new DbParam("Description",OWeeklySchedule.Description),
                        new DbParam("XmlData",OWeeklySchedule.WeeklyScheduleItemText),
                        new DbParam("AssetCode",AssetCode),
                        new DbParam("DataLanguage",DataLanguage),
                    });
                for (int iIndex = 0; iIndex < WeeklyScheduleItems.Length; iIndex++)
                {
                    DBService.Insert(
                       dbTrans, "TableWeeklyScheduleItem",
                       new DbParam[]
                       {
                            new DbParam("WeeklyScheduleItemId",System.Guid.NewGuid().ToString()),
                            new DbParam("ScheduleDate",WeeklyScheduleItems[iIndex].ScheduleDate),
                            new DbParam("DayOfWeek",WeeklyScheduleItems[iIndex].DayOfWeek),
                            new DbParam("Time",WeeklyScheduleItems[iIndex].Time),
                            new DbParam("ForeColor",WeeklyScheduleItems[iIndex].ForeColor),
                            new DbParam("BelongSite",WeeklyScheduleItems[iIndex].BelongSite),
                            new DbParam("frkWeeklyScheduleId",WeeklyScheduleItems[iIndex].frkWeeklyScheduleId),
                            new DbParam("ScheduleText",WeeklyScheduleItems[iIndex].ScheduleText),
                    });
                }
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                dbTrans.Rollback();
                throw (ex);
            }
        }


        public void DeleteWeeklySchedule(string SiteId, string WeeklyScheduleId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DBService.ExecuteNonQuery(null,
                "Delete from TableWeeklyScheduleItem where frkWeeklyScheduleId=@WeeklyScheduleId; " +
                "Delete from TableWeeklySchedule where WeeklyScheduleId=@WeeklyScheduleId",
                new DbParam[]{
                    new DbParam("WeeklyScheduleId",WeeklyScheduleId),
            });
        }
    }
}
